#!/usr/bin/python3
# -*- coding: UTF-8 -*-

import os
import time
import sqlite3
import config


def CreateTable(db_name):
    conn = sqlite3.connect(db_name)
    print("Opened database successfully")
    try:
        cmd = '''
            CREATE TABLE IF NOT EXISTS STUFF
            (AREA INT,
             NUM INT,
             NAME TEXT);
            '''
        conn.execute(cmd)
        conn.commit()
    except:
        print("Create table failed")
        return False

    conn.close()


def UpdateIntoTable(db_name, area, index, name):
    t_list = SelectTable(db_name, area, index)

    conn = sqlite3.connect(db_name)
    print("Opened database successfully")

    if len(t_list) != 0:
        cmd = "UPDATE STUFF SET NAME = \"%s\" \
                WHERE AREA=%d AND NUM=%d;" % (name, area, index)
    else:
        cmd = "INSERT INTO STUFF (AREA, NUM, NAME) \
                VALUES (%d, %d, \"%s\");" % (area, index, name)
    
    conn.execute(cmd)
    conn.commit()

    conn.close()


def InsertIntoTable(db_name, area, index, name):
    t_list = SelectTable(db_name, area, index)

    if len(t_list) != 0:
        print("%c-%03d-%s has already in DB" % (chr(area + ord('A')), index, name))
    else:
        conn = sqlite3.connect(db_name)
        print("Opened database successfully")

        cmd = "INSERT INTO STUFF (AREA, NUM, NAME) \
                VALUES (%d, %d, \"%s\");" % (area, index, name)
        conn.execute(cmd)
        conn.commit()

        conn.close()


def DeleteFromTable(db_name, area, index):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    print("Opened database successfully")

    cmd = "DELETE FROM STUFF WHERE AREA=%d AND NUM=%d;" % (area, index)
    print(cmd)
    c.execute(cmd)
    conn.commit()

    print("Total number of rows deleted :", conn.total_changes)
    conn.close()


def SelectTable(db_name, area, index):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    print("Opened database successfully")

    cmd = "SELECT * FROM STUFF WHERE AREA=%d AND NUM=%d;" % (area, index)
    table_array = c.execute(cmd)

    t_list = []
    for row in table_array:
        t_list.append(row)

    conn.close()
    return t_list


def SelectTableAll(db_name):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    print("Opened database successfully")

    cmd = "SELECT * FROM STUFF"
    table_array = c.execute(cmd)

    t_list = []
    for row in table_array:
        t_list.append(row)

    conn.close()
    return t_list


def main():
    CreateTable(config.DB_NAME)

    for i in range(0, 10):
        InsertIntoTable(config.DB_NAME, 0, i, "HSD%dCZJ" % i)

    all_data = SelectTableAll(config.DB_NAME)
    print("Step 1:")
    print(all_data)

    DeleteFromTable(config.DB_NAME, 0, 8)

    UpdateIntoTable(config.DB_NAME, 0, 6, "HSD-CZJ-6")

    all_data = SelectTableAll(config.DB_NAME)
    print("Step 2:")
    print(all_data)



if __name__ == '__main__':
    main()
